<?php
class Shop_Model_ListForRail extends Shop_Model_Base{

	/**
	 * 表示用変換
	 * @param $tenpoList
	 * @param $tenpoimageurl
	 * @param $linkurl
	 * @param $params
	 * 
	 * @return 変換後リスト
	 */
	public function convertList($tenpoList, $tenpoimageurl, $linkurl, $params)
	{
		$dispData = array();
		$preEkiCD = null;
		
		foreach ($tenpoList as $data) {

			//駅毎に店舗一覧を取得する
			if($preEkiCD != $data['M05_EKI_CD01']){
				$preEkiCD = $data['M05_EKI_CD01'];
				$dispData["$preEkiCD"]["shopList"] = array();
				$dispData["$preEkiCD"]['ekiName'] = $data['M03_EKI_NAME'];
			}
			
			// 店舗名変換
			$data['M05_TENPO_NAME'] = str_replace ('アパマンショップ', '', $data['M05_TENPO_NAME']);

			// 無料電話番号
			$callData = $this->convertFreeCalling($data);
			$data['IS_FREE_CALL']  = $callData['IS_FREE_CALL'];
			$data['TEL_01']		= $callData['TEL_01'];
			$data['TEL_02']		= $callData['TEL_02'];
			$data['hasFreeTel']	= $callData['hasFreeTel'];
			$data['CONNECTION_NO'] = $callData['CONNECTION_NO'];
			$data['M05_TENPO_LINE_ID'] = Apm_ShopUtil::getTenpoLineId($data['M05_TENPO_CD']);
			$data['LINE_URL'] = Apm_Config_Shop::URL_LINE;
			
			//高津情報変換
			$data['KOUTSUU_STR'] = Apm_ShopUtil::GetTrafficStr($data);
				
			// 営業時間変換
			$data['EIGYOU_ZIKAN'] = $this->convertEigyouZikan($data);
			
			// 定休日変換
			$data['M05_TEIKYUUBI_NAME'] = $this->convertTeikyuubi($data);
			
			// 郵便番号変換
			$data['M05_TENPO_YUUBIN'] = substr($data['M05_TENPO_YUUBIN'], 0, 3) . '-' . substr($data['M05_TENPO_YUUBIN'], 3, 4);
			
			// 店舗外観写真
			$data['IMAGE_PATH'] = $tenpoimageurl . 'g' . Apm_ShopUtil::getOldTenpoCd($data['M05_TENPO_CD']) . '.jpg';
			// スタッフ写真
			$data['STAFF_IMAGE_PATH'] = $tenpoimageurl . 's' . Apm_ShopUtil::getOldTenpoCd($data['M05_TENPO_CD']) . '.jpg';

			// リンク先URL
			$data['SHOP_DTL_URL'] = Apm_Bukkenurl::generateUrlByParametersShopDetail($data);
			if (!empty($params['OemCd'])) $data['SHOP_DTL_URL'] .= '?OemCd=' . $params['OemCd'];
			//$data['SHOP_DTL_URL'] = $linkurl . '?ShopCd=' . $data['M05_TENPO_CD'] . '&' . substr($params, 1);

			// AKBｷｬﾝﾍﾟｰﾝ:加盟店ﾒﾝﾊﾞｰｻｲﾄ追加対応
			$data['SHOP_PAGE_URL'] = Apm_Bukkenurl::generateUrlByParametersShopDetail(array_merge($data, array('Category'=>'9')));
			//$data['SHOP_PAGE_URL'] = str_replace("shopdtl","shoppage",$linkurl) . '?ShopCd=' . $data['M05_TENPO_CD'] . '&' . substr($params, 1);
			
			//$dispData[] = $data;
			$dispData["$preEkiCD"]["shopList"][] = $data;
		}
		
		//foreach ($dispData as $data)  echo $data["lstByEki"][0]['M05_TENPO_NAME'];
		return $dispData;
	}

	/**
	 * 表示用変換
	 * @param $tenpoList
	 * @param $tenpoimageurl
	 * @param $linkurl
	 * @param $params
	 *
	 * @return 変換後リスト
	 */
	public function convertListForBuken($tenpoList, $tenpoimageurl, $linkurl, $params)
	{
		$dispData = array();
		
		foreach ($tenpoList as $data) {
	
			// 店舗名変換
			$data['M05_TENPO_NAME'] = str_replace ('アパマンショップ', '', $data['M05_TENPO_NAME']);
	
			// 無料電話番号
			$callData = $this->convertFreeCalling($data);
			$data['IS_FREE_CALL']  = $callData['IS_FREE_CALL'];
			$data['TEL_01']		= $callData['TEL_01'];
			$data['TEL_02']		= $callData['TEL_02'];
			$data['hasFreeTel']	= $callData['hasFreeTel'];
			$data['CONNECTION_NO'] = $callData['CONNECTION_NO'];
			$data['M05_TENPO_LINE_ID'] = Apm_ShopUtil::getTenpoLineId($data['M05_TENPO_CD']);
			$data['LINE_URL'] = Apm_Config_Shop::URL_LINE;
				
			// 営業時間変換
			$data['EIGYOU_ZIKAN'] = $this->convertEigyouZikan($data);
				
			// 定休日変換
			$data['M05_TEIKYUUBI_NAME'] = $this->convertTeikyuubi($data);
				
			// 郵便番号変換
			$data['M05_TENPO_YUUBIN'] = substr($data['M05_TENPO_YUUBIN'], 0, 3) . '-' . substr($data['M05_TENPO_YUUBIN'], 3, 4);
				
			// 店舗外観写真
			$data['IMAGE_PATH'] = $tenpoimageurl . 'g' . Apm_ShopUtil::getOldTenpoCd($data['M05_TENPO_CD']) . '.jpg';
			// スタッフ写真
			$data['STAFF_IMAGE_PATH'] = $tenpoimageurl . 's' . Apm_ShopUtil::getOldTenpoCd($data['M05_TENPO_CD']) . '.jpg';
	
			// リンク先URL
			$data['SHOP_DTL_URL'] = Apm_Bukkenurl::generateUrlByParametersShopDetail($data);
			if (!empty($params['OemCd'])) $data['SHOP_DTL_URL'] .= '?OemCd=' . $params['OemCd'];
			//$data['SHOP_DTL_URL'] = $linkurl . '?ShopCd=' . $data['M05_TENPO_CD'] . '&' . substr($params, 1);
	
			// AKBｷｬﾝﾍﾟｰﾝ:加盟店ﾒﾝﾊﾞｰｻｲﾄ追加対応
			$data['SHOP_PAGE_URL'] = Apm_Bukkenurl::generateUrlByParametersShopDetail(array_merge($data, array('Category'=>'9')));
			//$data['SHOP_PAGE_URL'] = str_replace("shopdtl","shoppage",$linkurl) . '?ShopCd=' . $data['M05_TENPO_CD'] . '&' . substr($params, 1);
				
			$dispData[] = $data;
			//$dispData["$preEkiCD"]["shopList"][] = $data;
		}
	
		//foreach ($dispData as $data)  echo $data["lstByEki"][0]['M05_TENPO_NAME'];
		return $dispData;
	}
	
	/**
	 * 表示用変換(API用)
	 * @param $tenpoList
	 *
	 * @return 変換後リスト
	 */
	public function convertMobileList($tenpoList)
	{
		$dispData = array();
		
		foreach ($tenpoList as $data) {
			
			// 店舗名変換
			$data['M05_TENPO_NAME'] = str_replace('アパマンショップ', '', $data['M05_TENPO_NAME']);
			
			//店舗名、法人名、住所、番地、備考をユニコードに変換
			$data['M05_TENPO_NAME'] = Apm_Util::replaceUnicode($data['M05_TENPO_NAME']);
			$data['M05_HOUZINMEI'] = Apm_Util::replaceUnicode($data['M05_HOUZINMEI']);
			$data['M05_JYUUSHO_TEXT'] = Apm_Util::replaceUnicode($data['M05_JYUUSHO_TEXT']);
			$data['M05_TENPO_BANCHI'] = Apm_Util::replaceUnicode($data['M05_TENPO_BANCHI']);
			$data['M05_TENPO_JYUUSHO_BIKOU'] = Apm_Util::replaceUnicode($data['M05_TENPO_JYUUSHO_BIKOU']);
			
			// 郵便番号変換
			$data['CONVERT_TENPO_YUUBIN'] = substr($data['M05_TENPO_YUUBIN'], 0, 3) . '-' . substr($data['M05_TENPO_YUUBIN'], 3, 4);
			
			// 定休日変換
			$data['CONVERT_TEIKYUUBI'] = $this->convertTeikyuubi($data);
			
			// 営業時間変換
			$data['CONVERT_EIGYOU_ZIKAN'] = Apm_Util::replaceUnicode($this->convertEigyouZikan($data));
			
			// 無料電話接続番号
			$tempData = Apm_ShopUtil::getCallingData($data);
			for ($call_no=1; $call_no<=Apm_Config_Shop::CALLNOTE_NO_MAX; $call_no++) {
				$controlFlag = $data['M93_CONTROL_FLAG'.$call_no];
				if (!($tempData['IS_FREE_CALL'] && $controlFlag == '0')) {
					// 未使用であれば、接続番号を空にする
					$data['M93_CONNECTION_NO'.$call_no] = "";
				}
			}
			// 無料電話番号
			if ($tempData['IS_FREE_CALL']) {
				$shopDtl['CONVERT_FREE_CALLING_NO'] = $tempData['TEL_01'];
			}
			
			$dispData[] = $data;
		}
		
		return $dispData;
	}

	/**
	 * 店舗一覧所得sql生成（沿線検索）
	 * 
	 * @param $params
	 * 		地図から検索から遷移：都道府県CD（必須）
	 * 		サービス内容から検索から遷移：都道府県CD（任意）、サービス内容（任意）
	 * @param $db_params
	 * @return sql文
	 */
	protected function generateSql($params, &$db_params)
	{
		// クエリ文字列
		$strQryPrefM03 = '';
		$strQryPrefM05 = '';
		$strQryEnsen = '';
		$strQryEki = '';
		$strQryWhere = '';
		$strQryFreecalling = '';
		
		// 検索区分
		$strSrhKbn = $params['SrhKbn'];
		// 都道府県コードリスト
		$PrefCdList = array();
		// 沿線コードリスト
		$RailCdList = array();
		// 駅コードリスト
		$StatCdList = array();
		
		// 都道府県・沿線コードリスト作成
		if (!empty($params['RailCd']))
		{
			foreach ($params['RailCd'] as $PrefRailCd)
			{
				if (strlen($PrefRailCd) == 4)
				{
					$RailCdList[] = $PrefRailCd;
				}
				else
				{
					$PrefCdList[] = substr($PrefRailCd, 0, 2);
					$RailCdList[] = substr($PrefRailCd, 2);
				}
			}
		
			if (!empty($params['PrefCd']))
			{
				foreach ($params['PrefCd'] as $PrefCd)
				{
					$PrefCdList[] = $PrefCd;
				}
			}
			
			$PrefCdList = array_unique($PrefCdList);
			
			// 条件作成(都道府県)
			if (count($PrefCdList) > 0)
			{
				$strQryPrefM03 .= ' AND M03_TODOUFUKEN_CD ' . $this->generateWhereIn("prefCdM03",       $PrefCdList, $db_params);
				$strQryPrefM05 .= ' AND M05_TENPO_TODOUFUKEN_CD ' . $this->generateWhereIn("prefCdM05", $PrefCdList, $db_params);
			}
			
			// 条件作成処理(沿線)
			if (count($RailCdList) > 0)
			{
				$strQryEnsen .= ' AND M02_ENSEN_CD ' . $this->generateWhereIn("ensenCd", $RailCdList, $db_params);
			}
			
			// 条件生成(サービスから検索)
			$strQryWhere .= $this->generateCondService($params);
			
			// ダミー店舗除外
			$strQryWhere .= $this->generateNotinDummyShop("M05TENPO_INFO.M05_TENPO_CD", $db_params);
			
			// 無料電話番号
			for ($call_no=1; $call_no<=Apm_Config_Shop::CALLNOTE_NO_MAX; $call_no++) {
				$strQryFreecalling .= " ,M93_CONNECTION_NO{$call_no} ";
				$strQryFreecalling .= " ,M93_CONTROL_FLAG{$call_no} ";
			}
				
		}
		//駅で探す
		else if (!empty($params['StCd']))
		{
			foreach ($params['StCd'] as $StatCd)
			{
				$StatCdList[] = $StatCd;
			}
			
			// 条件作成処理(駅)
			if (count($StatCdList) > 0)
			{
				$strQryEki .= ' AND M03_EKI_CD ' . $this->generateWhereIn("ekiCd", $StatCdList, $db_params);
			}
		}
		
$sql = <<< DOC_END

SELECT
	 Z1.M05_TENPO_CD
	,M05_HOUZIN_CD
	,M05_TENPO_NAME
	,M05_HOUZINMEI
	,M05_JYUUSHO_TEXT
	,M05_TENPO_BANCHI
	,M05_TENPO_JYUUSHO_BIKOU
	,M05_INFORMATION
	,M05_KAITENBI
	,M05_TENPO_TODOUFUKEN
	,M05_TENPO_SHIKUCHOUSON
	,M05_EIGYOU_ZIKAN_CLOSE_H
	,M05_EIGYOU_ZIKAN_CLOSE_M
	,M05_MIDDLE_FLAG
	,M05_TENPO_TEL01
	,M05_TENPO_TEL02
	,CASE
		WHEN LPAD(NVL(M05_EIGYOU_ZIKAN_CLOSE_H, ''), 2, '0') || LPAD(NVL(M05_EIGYOU_ZIKAN_CLOSE_M, ''), 2, '0') > '1900' THEN 1
		ELSE 0
	END AS M05_EIGYOU_ZIKAN_CLOSE
	,M05_OEM
	,SUBSTR(SV.M05_SERVICE_FLAG, 12, 1) AS INFOMATION_CENTER_FLAG
	,M05_TENPO_YUUBIN
	,M05_KOUTSUU_TEXT01
	,M05_KOUTSUU_BIKOU01
	,M05_TOHO01
	,M05_TEIKYUUBI
	,M05_EIGYOU_ZIKAN_OPEN_H
	,M05_EIGYOU_ZIKAN_OPEN_M
	,M05_EIGYOU_ZIKAN_BIKOU
	,M05_EKI_CD01
	,M03_EKI_CD
	,M03_EKI_NAME
	,M05_TOKUCHO01
	,M05_TOKUCHO02
	,M05_TOKUCHO03
	,M05_TOKUCHO04
	,M05_TOKUCHO05
	,M05_TOKUCHO06
	,M05_TOKUCHO07
	,M05_TOKUCHO08
	,M05_TOKUCHO09
	,M05_TOKUCHO10
	,M05_TOKUCHO11
	,M05_TOKUCHO12
	,M05_TOKUCHO13
	,M05_TOKUCHO14
	,M05_TOKUCHO15
	,M05_TOKUCHO16
	,M05_ENSEN_CD01
	,M03_ENSEN_NAME
	,M02.M02_ENSEN_KUBUN
	{$strQryFreecalling}
	,M93_USE_KBN
	,M93_START_DATE
	,M93_DEL_DATE
FROM
	 M05TENPO_INFO Z1
	,M05TENPO_SERVICE SV
	,M03EKI M03
	,M02ENSEN M02
	,M93CALLNOTE M93
	,(
		SELECT
			 M05_TENPO_CD
			,CEIL(DBMS_RANDOM.VALUE*1000000) RANDOM
		FROM
			 M02ENSEN E
			,(
				SELECT
					 M05_TENPO_CD
					,M05_TENPO_TODOUFUKEN_CD
					,M03_ENSEN_CD
					,M03_EKI_CD
				FROM
					(
						SELECT
							 M03_ENSEN_CD
							,M03_TODOUFUKEN_CD
							,M03_RENRAKU_EKI_CD
							,M03_EKI_CD
						FROM
							 M03EKI
						WHERE
							M03_RENRAKU_EKI_CD is not null
						{$strQryPrefM03}
					) A
					,(
						SELECT
							 M05_TENPO_CD
							,M05_TENPO_TODOUFUKEN_CD
							,M03_RENRAKU_EKI_CD as M05_RENRAKU_EKI_CD
						FROM
							 M05TENPO_INFO
							,M03EKI
						WHERE
							M05_EKI_CD01 = M03_EKI_CD
						AND M05_KOUKAI_FLAG = 1
						AND ( M05_TENPO_CD > '01000000' AND M05_TENPO_CD < '48000000' )
						AND M05_TENPO_TODOUFUKEN is not null
						AND M05_TENPO_SHIKUCHOUSON is not null
						AND M03_RENRAKU_EKI_CD is not null
						{$strQryPrefM03}
						{$strQryPrefM05}
						{$strQryWhere}
					) B
				WHERE
					M03_TODOUFUKEN_CD = M05_TENPO_TODOUFUKEN_CD
				AND M03_RENRAKU_EKI_CD = M05_RENRAKU_EKI_CD
			UNION
				SELECT
					 M05_TENPO_CD
					,M05_TENPO_TODOUFUKEN_CD
					,M03_ENSEN_CD
					,M03_EKI_CD
				FROM
					 M05TENPO_INFO
					,M03EKI
				WHERE
					M05_EKI_CD01 = M03_EKI_CD
				AND M05_KOUKAI_FLAG = 1
				AND ( M05_TENPO_CD > '01000000' AND M05_TENPO_CD < '48000000' )
				AND M03_RENRAKU_EKI_CD is null
				AND M05_TENPO_TODOUFUKEN is not null
				AND M05_TENPO_SHIKUCHOUSON is not null
				{$strQryPrefM03}
				{$strQryPrefM05}
				{$strQryWhere}
			) F
		WHERE
			E.M02_ENSEN_CD = F.M03_ENSEN_CD
		{$strQryEnsen}
		{$strQryEki}
		GROUP BY
			 M05_TENPO_CD
	) Z2
WHERE
	Z1.M05_TENPO_CD = Z2.M05_TENPO_CD
AND Z1.M05_TENPO_CD = SV.M05_TENPO_CD
AND Z1.M05_EKI_CD01 = M03.M03_EKI_CD(+)
AND M03.M03_ENSEN_CD = M02.M02_ENSEN_CD(+)
AND Z1.M05_TENPO_CD = M93.M93_TENPO_CD(+)
ORDER BY
	/*,M05_TENPO_TODOUFUKEN_CD ASC
	,M05_TENPO_SHIKUCHOUSON_CD ASC*/
	M03_EKI_CD
	,RANDOM

DOC_END;
		//echo $sql;
		return $sql;
	}
}
?>
